package concesionaria;

import javax.swing.JInternalFrame;
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;

public class VentanaInscClientePlan extends JInternalFrame implements ItemListener, ActionListener {

    static int openFrameCount = 0;
    static int ventanaNro = 100 * 1;
    static final int xOffset = ventanaNro + 30, yOffset = 30;
    JButton bu1, bu2, bu, refrescar;
    JPanel contentPane = new JPanel();
    PanelRegCliente contentPane2 = new PanelRegCliente();
    JComboBox empleados = new JComboBox();
    JComboBox clientes = new JComboBox();
    JComboBox auto = new JComboBox();
    JComboBox plan = new JComboBox();
    JLabel p = new JLabel("Selecione Plan");
    String pla[] = new String[40];
    String plaid[] = new String[40];
    JLabel e = new JLabel("Seleccione Empleado");
    JLabel c = new JLabel("Seleccione Cliente");
    JLabel a = new JLabel("Seleccione Auto");
    String emp[] = new String[40];
    String cli[] = new String[40];
    String cliid[] = new String[40];
    String aut[] = new String[40];
    String autid[] = new String[40];
    String elecciones[] = new String[5];

    public VentanaInscClientePlan() {
        super("Inscribir cliente a Plan",
                true, //resizable
                true, //closable
                true, //maximizable
                true);//iconifiable

        //...Create the GUI and put it in the window...
        bu = new JButton("Registrar Nuevo Cliente");
        refrescar = new JButton("Refrescar");
        bu1 = new JButton("Cancelar");
        bu2 = new JButton("Inscribir");
        refrescar.setActionCommand("refrescar");
        bu.setActionCommand("registrar");
        bu1.setActionCommand("cancelar");
        bu2.setActionCommand("inscribir");
        ResultSet rs = null;
        int i = 0;
        //SQL query command
        try {

            String SQL = "SELECT * FROM persona WHERE tipo LIKE 'emp'";


            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                emp[i] = new String(rs.getString("numero") + " : " + rs.getString("nombre") + " " + rs.getString("apellido"));
                i++;
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e.getMessage());
        }
        empleados.addItem(" ");
        for (int j = 0; j < i; j++) {
            empleados.addItem(emp[j]);
        }
        empleados.addItemListener(this);
        i = 0;
        try {
            String SQL = "SELECT * FROM persona WHERE tipo LIKE 'cli'";
            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                cli[i] = rs.getString("numero") + " : " + rs.getString("nombre") + " " + rs.getString("apellido");
                cliid[i] = rs.getString("ID");
                i++;
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e.getMessage());
        }
        clientes.addItem(" ");
        for (int j = 0; j < i; j++) {
            clientes.addItem(cli[j]);
        }
        clientes.addItemListener(this);
        i = 0;
        try {
            String SQL = "SELECT * FROM auto";
            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                autid[i] = rs.getString("ID");
                aut[i] = rs.getString("marca") + " : " + rs.getString("modelo");
                i++;
            }
        } catch (SQLException e4) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e4.getMessage());
        }
        auto.addItem(" ");
        for (int j = 0; j < i; j++) {
            auto.addItem(aut[j]);
        }
        auto.addItemListener(this);
        i = 0;
        try {
            String SQL = "SELECT * FROM plan";
            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                pla[i] = new String(rs.getString("nombre"));
                i++;
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e.getMessage());
        }
        plan.addItem(" ");
        plan.addItemListener(this);
        refrescar.addActionListener(this);
        bu.addActionListener(this);
        bu1.addActionListener(this);
        bu2.addActionListener(this);



        contentPane.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));
        contentPane.setLayout(new BoxLayout(contentPane,
                BoxLayout.PAGE_AXIS));
        contentPane.add(e);
        contentPane.add(empleados);
        contentPane.add(c);
        contentPane.add(clientes);
        contentPane.add(bu, BorderLayout.CENTER);
        contentPane.add(a);
        contentPane.add(auto);
        contentPane.add(p);
        contentPane.add(plan);

        contentPane.add(bu1, BorderLayout.WEST);
        contentPane.add(bu2, BorderLayout.EAST);

        contentPane.add(refrescar, BorderLayout.EAST);
        setContentPane(contentPane);
        setVisible(
                true);
        //...Then set the window size or call pack...
//        setSize( 240, 300);
        pack();

        //Set the window's location.
        openFrameCount++;
        setLocation(ventanaNro + ((xOffset - (xOffset - 30)) * openFrameCount), yOffset * openFrameCount);

    }

    public void Redibujar() {
        contentPane.removeAll();
        clientes.removeAllItems();
        empleados.removeAllItems();
        auto.removeAllItems();
        plan.removeAllItems();
        ResultSet rs = null;
        int i = 0;
        //SQL query command
        try {

            String SQL = "SELECT * FROM persona WHERE tipo LIKE 'emp'";


            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                emp[i] = new String(rs.getString("numero") + " : " + rs.getString("nombre") + " " + rs.getString("apellido"));
                i++;
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e.getMessage());
        }
        empleados.addItem(" ");
        for (int j = 0; j < i; j++) {
            empleados.addItem(emp[j]);
        }
        empleados.addItemListener(this);
        i = 0;
        try {
            String SQL = "SELECT * FROM persona WHERE tipo LIKE 'cli'";
            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                cli[i] = rs.getString("numero") + " : " + rs.getString("nombre") + " " + rs.getString("apellido");
                cliid[i] = rs.getString("ID");
                i++;
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e.getMessage());
        }
        clientes.addItem(" ");
        for (int j = 0; j < i; j++) {
            clientes.addItem(cli[j]);
        }
        clientes.addItemListener(this);
        i = 0;
        try {
            String SQL = "SELECT * FROM auto";
            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                autid[i] = rs.getString("ID");
                aut[i] = rs.getString("marca") + " : " + rs.getString("modelo");
                i++;
            }
        } catch (SQLException e4) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e4.getMessage());
        }
        auto.addItem(" ");
        for (int j = 0; j < i; j++) {
            auto.addItem(aut[j]);
        }
        auto.addItemListener(this);
        i = 0;
        try {
            String SQL = "SELECT * FROM plan";
            Statement stmt;
            stmt = DBCon.getConexion().createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                pla[i] = new String(rs.getString("nombre"));
                i++;
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error en SQL: " + e.getMessage());
        }
        plan.addItem(" ");
        plan.addItemListener(this);
        contentPane.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));
        contentPane.setLayout(new BoxLayout(contentPane,
                BoxLayout.PAGE_AXIS));
        contentPane.add(e);
        contentPane.add(empleados);
        contentPane.add(c);
        contentPane.add(clientes);
        contentPane.add(bu, BorderLayout.CENTER);
        contentPane.add(a);
        contentPane.add(auto);
        contentPane.add(p);
        contentPane.add(plan);

        contentPane.add(bu1, BorderLayout.WEST);
        contentPane.add(bu2, BorderLayout.EAST);

        contentPane.add(refrescar, BorderLayout.EAST);
        setContentPane(contentPane);
    }

    public void itemStateChanged(ItemEvent event) {
        int control = 0;
        Object i = event.getSource();
        if (i == clientes) {
            int eleccion = clientes.getSelectedIndex() - 1;
            if (eleccion < 0) {
                elecciones[1] = "0";
            } else {
                elecciones[1] = cli[eleccion];
            }
        }
        if (i == empleados) {
            int eleccion = empleados.getSelectedIndex() - 1;
            if (eleccion < 0) {
                elecciones[0] = "0";
            } else {
                elecciones[0] = emp[eleccion];
            }
        }
        if (i == auto) {
            int eleccion = auto.getSelectedIndex() - 1;
            if (eleccion < 0) {
                plan.removeAllItems();
                plan.addItem(" ");
                setVisible(true);
                add(p);
                plan.addItemListener(this);
                add(plan);
                control = 1;
                bu1.removeAll();
                bu2.removeAll();
                add(bu1);
                add(bu2);
                elecciones[2] = "0";
            } else {
                String s = aut[eleccion];
                elecciones[2] = aut[eleccion];
                int aux = 0;
                try {
                    String SQL = "SELECT plan.ID, plan.nombre FROM aut_plan, plan WHERE plan.ID=aut_plan.ID_plan AND aut_plan.ID_auto = " + autid[auto.getSelectedIndex() - 1];
                    Statement stmt;
                    stmt = DBCon.getConexion().createStatement();
                    ResultSet rs = stmt.executeQuery(SQL);
                    while (rs.next()) {
                        pla[aux] = rs.getString("plan.nombre");
                        plaid[aux] = rs.getString("ID");
                        aux++;
                    }
                } catch (SQLException e) {
                    JOptionPane.showMessageDialog(null, "Error en SQL2: " + e.getMessage());
                }
                plan.removeAllItems();
                plan.addItem(" ");
                for (int j = 0; j < aux; j++) {
                    plan.addItem(pla[j]);
                }
                setVisible(true);
                add(p);
                plan.addItemListener(this);
                add(plan);
                control = 1;
                refrescar.removeAll();
                bu1.removeAll();
                bu2.removeAll();
                add(bu1);
                add(bu2);
                contentPane.add(refrescar);
            }
        }
        if (i == plan) {
            int eleccion = plan.getSelectedIndex() - 1;
            if (eleccion < 0) {
                elecciones[3] = "0";
            } else {
                elecciones[3] = pla[eleccion];
            }
        }
    }

    @Override
    public void actionPerformed(ActionEvent e) {

        int error = 0;

        Object fuente = e.getActionCommand();
        if (fuente == "cancelar") {
            openFrameCount--;
            this.setVisible(false);
        }
     
        
        if (fuente == "inscribir") {
            for (int i = 0; i < 4; i++) {
                if ("0".equals(elecciones[i]) || elecciones[i] == null) {
                    JOptionPane.showMessageDialog(null, "No ha completado con todos los campos", "verifique los datos", 2);
                    error = 1;
                    i = 4;
                }
            }
            if (error == 0) {
                int resultado = JOptionPane.showConfirmDialog(null, "Confirmar inscrpcion\n" + elecciones[0] + "\n" + elecciones[1] + "\n" + elecciones[2] + "\n" + elecciones[3], null, JOptionPane.YES_NO_OPTION);
                if (resultado == 0) {
                    try {
                        String SQL = "select count(*) from inscripcion where ID_cli= " + cliid[clientes.getSelectedIndex() - 1] + " AND ID_plan = " + plaid[plan.getSelectedIndex() - 1] + " AND ID_auto= " + autid[auto.getSelectedIndex() - 1];
                        Statement stmt;
                        stmt = DBCon.getConexion().createStatement();
                        ResultSet rs = stmt.executeQuery(SQL);
                        while (rs.next()) {
                            if ("1".equals(rs.getString(1))) {
                                error = 1;
                                JOptionPane.showMessageDialog(null, "El cliente que desea inscribir ya se encuentra inscipto");
                            }

                        }

                        //INSERT INTO concesionaria.inscripcion (`ID_cli`, `ID_plan`, `ID_auto`, fecha) VALUES (2, 1, 1, '2011-10-05')
                    } catch (SQLException e1) {
                        JOptionPane.showMessageDialog(null, "Error en SQL2: " + e1.getMessage());

                    }
                    if (error == 0) {
                        try {
                            java.util.Date fechaActual = new java.util.Date();
                            SimpleDateFormat formato = new SimpleDateFormat("yyyy'-'MM'-'dd");
                            String cadenaFecha = formato.format(fechaActual);
                            String SQL = "INSERT INTO concesionaria.inscripcion (`ID_cli`, `ID_plan`, `ID_auto`, fecha) VALUES (" + cliid[clientes.getSelectedIndex() - 1] + " ," + plaid[plan.getSelectedIndex() - 1] + "," + autid[auto.getSelectedIndex() - 1] + ",'" + cadenaFecha + "')";
                            Statement stmt;
                            stmt = DBCon.getConexion().createStatement();
                            stmt.executeUpdate(SQL);


                            //INSERT INTO concesionaria.inscripcion (`ID_cli`, `ID_plan`, `ID_auto`, fecha) VALUES (2, 1, 1, '2011-10-05')
                        } catch (SQLException e1) {
                            JOptionPane.showMessageDialog(null, "Error en SQL2: " + e1.getMessage());

                        }
                        JOptionPane.showMessageDialog(null, "La inscripcion ha sido realizada con exito");
                        this.setVisible(false);
                    }
                }

            }

        }
        if ("registrar".equals(fuente)) {
            contentPane2 = new PanelRegCliente();
            contentPane2.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));
            contentPane2.setLayout(new BoxLayout(contentPane2,
                    BoxLayout.PAGE_AXIS));
            contentPane.add(contentPane2);
            pack();

        }

        if ("refrescar".equals(fuente)) {
            Redibujar();
            pack();
        }
    }
}
